package com.sgcc.electricjournal.util;

//import com.github.pagehelper.StringUtil;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExportExcelUtil<T> {


    //导出文件的标题
    public String title;
    //导出文件文件名
    public String fileName;
    //导出文件列名
    public Object[] colNames;
    //导出实体类的字段属性名称
    public Object[] attrNames;
    //导出数据的List
    public List<T> dataList;

    public ExportExcelUtil(String title, String fileName, Object[] colNames, Object[] attrNames, List<T> dataList) {
        this.title = title;
        this.fileName = fileName;
        this.colNames = colNames;
        this.attrNames = attrNames;
        this.dataList = dataList;
    }

    public ExportExcelUtil() {
    }


    //public void commonExport(HttpServletResponse response){
    public HSSFWorkbook commonExport(){

        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建sheet
            HSSFSheet sheet = workbook.createSheet();
            //创建第一行  行标题
            HSSFRow row = sheet.createRow(0);
            //单元格
            HSSFCell titleCell = row.createCell(0);
            //title样式
            HSSFCellStyle rowTopStyle = getTitleRowStyle(workbook);
            //数据单元格样式
            HSSFCellStyle rowCellStyle = getStyle(workbook);
            //合并单元格,标题占两行
            sheet.addMergedRegion(new CellRangeAddress(0,1,0,(colNames.length-1)));
            titleCell.setCellStyle(rowTopStyle);
            titleCell.setCellValue(title);

            //定义列数
            int columNum = colNames.length;
            //将列头写入excel
            HSSFRow rowArr = sheet.createRow(2);
            for(int n = 0; n<columNum; n++){

                //创建列头对应个数的单元格
                HSSFCell cellRowTop = rowArr.createCell(n);
                //设置列头单元格的数据类型1
                cellRowTop.setCellType(HSSFCell.CELL_TYPE_STRING);
                HSSFRichTextString text = new HSSFRichTextString(colNames[n]!=null?colNames[n].toString():"");
                //设置列头单元格的值
                cellRowTop.setCellValue(text);
                //设置列头单元格样式
                cellRowTop.setCellStyle(rowTopStyle);

            }

            //将正文数据写入到对应的单元格
            if(dataList != null && dataList.size()>0){
                for (int i = 0 ; i<dataList.size(); i++){
                    Object obj = dataList.get(i);
                    HSSFRow rowCell = sheet.createRow(i + 3);
                    Class<?> claz = obj.getClass();
                    if(claz.isAssignableFrom((java.util.HashMap.class))){
                        //datalist中是hashmap处理方法
                        Map<String,Object> map =  (Map<String,Object>)obj;
                        for (int j = 0; j < attrNames.length;j++){
                            HSSFCell cell = rowCell.createCell(j, HSSFCell.CELL_TYPE_STRING);

                            if((map.get(attrNames[j]))!=null){
                                cell.setCellValue(map.get(attrNames[j]).toString());
                            }else {
                                cell.setCellValue("");
                            }
                            cell.setCellStyle(rowCellStyle);
                        }
                    }else if(claz.isAssignableFrom(java.lang.Object[].class)){
                        //Object 数组处理
                        Object[] objs = (Object[])obj;
                        for (int j = 0; j<objs.length;j++){

                            HSSFCell cell = rowCell.createCell(j, HSSFCell.CELL_TYPE_STRING);
                            if(objs[j] != null){
                                //设置单元格的值
                                cell.setCellValue(objs[j].toString());
                            }else {
                                cell.setCellValue("");
                            }
                            //设置单元格样式
                            cell.setCellStyle(rowCellStyle);
                        }
                    }else {
                        //实体类处理
                        if(attrNames != null && attrNames.length>0){
                            for(int j = 0; j<attrNames.length;j++){
                                String fdName = attrNames[j].toString();
                                StringBuilder sb =  new StringBuilder(fdName);
                                if(!Character.isUpperCase(sb.charAt(0))){
                                    sb.setCharAt(0, Character.toUpperCase(sb.charAt(0)));
                                }

                                Method method = claz.getMethod("get" + sb.toString(), null);
                                Object value = method.invoke(obj, null);
                                String valueStr = value ==null?"":value.toString();
                                if(value instanceof Date && null != value){
                                    valueStr = DateUtil.format((Date) value,DateUtil.PATTERN_19);
                                }
                                HSSFCell cell = null;
                                if(isNumeric(valueStr)){
                                    cell = rowCell.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
                                    BigDecimal bigDecimal = CommonUtil.getBigdeCimalFromString(valueStr);
                                    if(null != bigDecimal && bigDecimal.compareTo(new BigDecimal(Double.MAX_VALUE))<0){
                                        cell.setCellValue(bigDecimal.doubleValue());
                                    }else {
                                        cell = rowCell.createCell(j,HSSFCell.CELL_TYPE_STRING);
                                        cell.setCellValue(valueStr);
                                    }
                                }else {
                                    cell = rowCell.createCell(j,HSSFCell.CELL_TYPE_STRING);
                                    cell.setCellValue(valueStr);
                                }
                                cell.setCellStyle(rowCellStyle);
                            }
                        }
                    }
                }
            }else {
                System.out.println("数据不存在");
            }
            //让列宽自动适应
            for(int colNum=0;colNum<columNum;colNum++){
                int columnWidth = sheet.getColumnWidth(colNum)/256;
                for(int rowNum = 0; rowNum<sheet.getLastRowNum();rowNum++){
                    HSSFRow currentRow;
                    //当前行未被使用过
                    if(sheet.getRow(rowNum)==null){
                        currentRow = sheet.createRow(rowNum);
                    }else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    if(currentRow.getCell(colNum)!= null){
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if(currentCell.getCellType()==HSSFCell.CELL_TYPE_STRING){
                            int length = currentCell.getStringCellValue().getBytes().length;
                            if(columnWidth < length){
                                columnWidth = length;
                            }
                        }
                    }
                }
                if(colNum == 0){
                    sheet.setColumnWidth(colNum, Math.min(255*256,(columnWidth - 2)*256));
                }else {
                    sheet.setColumnWidth(colNum, Math.min(255*256, (columnWidth+4)*256));
                }
            }
            //将序号相同的合并单元格
            workbook =mergeCell(workbook);
            return workbook;

        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException();

        }

    }

    /**
     * 序号合并单元格
     * @param workbook
     */
    public HSSFWorkbook mergeCell(HSSFWorkbook workbook) {

        HSSFSheet sheet = workbook.getSheetAt(0); //得到第一个sheet
        int rowNo = sheet.getLastRowNum()+1; //得到行数

        List<Integer> serialNumList = new ArrayList<>();

        if(rowNo>=3){
            for(int i = 3;i<rowNo;i++){//excel中是从第4行开始
                sheet.getRow(i).getCell(0).setCellType(Cell.CELL_TYPE_STRING);//转换类型
                Integer value = Integer.valueOf(sheet.getRow(i).getCell(0).getStringCellValue());//获取每行序号
                //System.out.println("value==="+value);
                serialNumList.add(value);
            }


            List<Num> list=new ArrayList<Num>();
            for(int i=0;i<serialNumList.size();i++){
                if(!isExist(serialNumList.get(i),list)){
                    Num n=new Num();//值,值的个数
                    n.num=serialNumList.get(i);
                    n.n=1;
                    list.add(n);
                }
            }
            int p = 0;
            for(Iterator<Num> it=list.iterator();it.hasNext();){
                Num nu=it.next();
                //System.out.println(nu.num+":"+nu.n);

                if(1 != nu.n){
                    /*
                    说明：
                        1, // 起始行
                        1, // 结束行
                        0, // 起始列
                        2  // 结束列
                    */
                    sheet.addMergedRegion(new CellRangeAddress(nu.num+2+p, nu.num+2+nu.n-1+p, 0, 0));
                    p = p + nu.n -1;
                }

            }
        }



        return workbook;
    }

    /**
     *
     * @param num
     * @param list
     * @return
     */
    public  boolean isExist(int num,List<Num> list){
        boolean b=false;
        for(Iterator<Num> it = list.iterator(); it.hasNext();){
            Num nu=it.next();
            if((nu.num==num)){
                nu.n++;
                b=true;
                break;
            }
        }
        return b;
    }


    public void NormalExport(HttpServletResponse response, JSONObject json) {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建sheet
            HSSFSheet sheet = workbook.createSheet();
            //创建第一行标题行
            HSSFRow row = sheet.createRow(0);
            //单元格
            HSSFCell titleCell = row.createCell(0);
            //title样式
            HSSFCellStyle rowTopStyle = getTitleRowStyle(workbook);
            HSSFCellStyle rowpurpleTopStyle = getPurpleTitleRowStyle(workbook);

            //数据单元格样式
            HSSFCellStyle rowCellStyle = getStyle(workbook);
            //color90底色红色,字体居中
            HSSFCellStyle bluerowCellStyle = getBlueStyle(workbook);

            //合并单元格,标题占两行
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (colNames.length - 1)));
            titleCell.setCellStyle(rowTopStyle);
            titleCell.setCellValue(title);
            //定义列数
            int columNum = colNames.length;
            //将列头写入excel
            HSSFRow rowArr = sheet.createRow(2);
            rowArr.setHeight((short) 800);
            for (int n = 0; n < columNum; n++) {
                HSSFCell cellRowTop = rowArr.createCell(n);//创建列头对应个数的单元格
                cellRowTop.setCellType(HSSFCell.CELL_TYPE_STRING);//设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(colNames[n] != null ? colNames[n].toString() : "");
                cellRowTop.setCellValue(text);//设置列头单元格的值
                cellRowTop.setCellStyle(rowpurpleTopStyle);//设置列头单元格样式
            }
            //将正文数据写入到对应的单元格
            if (dataList != null && dataList.size() > 0) {
                for (int i = 0; i < dataList.size(); i++) {
                    Object obj = dataList.get(i);
                    HSSFRow rowCell = sheet.createRow(i + 3);
                    Class<?> claz = obj.getClass();
                    if (claz.isAssignableFrom(java.util.HashMap.class)) {
                        //dataList中是hashMap处理方法
                        Map<String, Object> map = (Map<String, Object>) obj;
                        //Object[] keys = map.keySet().toArray();
                        for (int j = 0; j < attrNames.length; j++) {
                            HSSFCell cell = rowCell.createCell(j, HSSFCell.CELL_TYPE_STRING);
                            if (map.get(attrNames[j]) != null) {
                                cell.setCellValue(map.get(attrNames[j]).toString());
                            } else {
                                cell.setCellStyle(rowCellStyle);
                            }
                            cell.setCellStyle(rowCellStyle);
                        }
                    } else if (claz.isAssignableFrom(java.lang.Object[].class)) {
                        //Object数组处理
                        Object[] objs = (Object[]) obj;
                        for (int j = 0; j < objs.length; j++) {
                            HSSFCell cell = rowCell.createCell(j, HSSFCell.CELL_TYPE_STRING);
                            if (objs[j] != null) {
                                cell.setCellValue(objs[j].toString());//设置单元格的值
                            } else {
                                cell.setCellValue("");
                            }
                            cell.setCellStyle(rowCellStyle);//设置单元格样式
                        }
                    } else {
                        //自定义数据单元格标识位
                        //默认情况下,如果检测某值为数字类型,则创建一个HSSFCell.CELL_TYPE_NUMERIC类型的单元格并赋值
                        //当且仅当此值内为noNumeric 则取消数字检测,创建HSSFCell.CELL_TYPE_STRING单元格并赋值
                        String noNumeric = (String) StringUtil.ifNullToBlank((String) json.get("noNumeric"));
                        //实体类处理
                        if (attrNames != null && attrNames.length > 0) {
                            for (int j = 0; j < attrNames.length; j++) {
                                String fdName = attrNames[j].toString();
                                StringBuilder sb = new StringBuilder(fdName);
                                if (!Character.isUpperCase(sb.charAt(0))) {
                                    sb.setCharAt(0, Character.toUpperCase(sb.charAt(0)));
                                }
                                Method method = claz.getMethod("get" + sb.toString(), null);
                                Object value = method.invoke(obj, null);
                                String valueStr = value == null ? "" : value.toString();
                                HSSFCell cell = null;
                                if (StringUtil.equal(noNumeric, "noNumeric")) {
                                    cell = rowCell.createCell(j, HSSFCell.CELL_TYPE_STRING);
                                    cell.setCellValue(valueStr);
                                } else {
                                    if (isNumeric(valueStr)) {
                                        cell = rowCell.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
                                        BigDecimal bigDecimal = CommonUtil.getBigdeCimalFromString(valueStr);
                                        if (null != bigDecimal && bigDecimal.compareTo(new BigDecimal(Double.MAX_VALUE)) < 0) {
                                            cell.setCellValue(bigDecimal.doubleValue());

                                        } else {
                                            cell = rowCell.createCell(j, HSSFCell.CELL_TYPE_STRING);
                                            cell.setCellValue(valueStr);
                                        }
                                    } else {
                                        cell = rowCell.createCell(j, HSSFCell.CELL_TYPE_STRING);
                                        cell.setCellValue(valueStr);
                                    }
                                }
                                cell.setCellStyle(rowCellStyle);
                            }
                        }
                    }
                }
            } else {
                System.out.println("数据不存在");
            }
            //让列宽自动适应
            for (int colNum = 0; colNum < columNum; colNum++) {
                sheet.autoSizeColumn(colNum, true);
            }
            List<Integer> colWidthList = (List<Integer>) json.get("colWidthList");
            if (null != colWidthList) {
                for (int i = 0; i < colWidthList.size(); i++) {
                    sheet.setColumnWidth(i, Math.min(255 * 256, colWidthList.get(i)));
                }
            }
            List<HashMap<String, Object>> cellcolorConfigList = (List<HashMap<String, Object>>) json.get("cellcolorConfigList");
            //--------------update:--根据配置信息 对单元格进行自定义设定颜色----------------start-----------------
            if (null != cellcolorConfigList && !cellcolorConfigList.isEmpty()) {
                for (HashMap<String, Object> cellcolorConfigitem : cellcolorConfigList) {
                    if (null != cellcolorConfigitem) {
                        String key = (String)StringUtil.ifNullToBlank( (String) cellcolorConfigitem.get("key"));
                        String value = (String)StringUtil.ifNullToBlank( (String) cellcolorConfigitem.get("value"));
                        String keyWord = (String) StringUtil.ifNullToBlank((String) cellcolorConfigitem.get("keyWord"));
                        String color =(String) StringUtil.ifNullToBlank( (String) cellcolorConfigitem.get("color"));
                        List<String> attrNames = (List<String>)StringUtil.ifNullToBlank( (String) cellcolorConfigitem.get("attrNames"));
                        if (com.github.pagehelper.StringUtil.isNotEmpty(key) && (com.github.pagehelper.StringUtil.isNotEmpty(value) || com.github.pagehelper.StringUtil.isNotEmpty(keyWord)) && com.github.pagehelper.StringUtil.isNotEmpty(color)
                                && (null != attrNames && attrNames.isEmpty())) {
                            HSSFCellStyle colorCellStyle = getStyle(workbook);
                            if (StringUtil.equal(color, "fontRed")) {
                                colorCellStyle = getRedStyle(workbook);
                            }
                            if (StringUtil.equal(color, "fontBlue")) {
                                colorCellStyle = getNormalBlueStyle(workbook);
                            }
                            for (int startRowIndex = 3; startRowIndex < (dataList.size() + 2); startRowIndex++) {
                                Boolean dataEque = comPareValueWithlistByData(startRowIndex, key, value, keyWord);
                                if (dataEque) {
                                    for (String attrNameitem : attrNames) {
                                        int attrNameitemcellnum = getMyindexFromAttrNames(attrNameitem);
                                        sheet.getRow(startRowIndex).getCell(attrNameitemcellnum).setCellStyle(colorCellStyle);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            //--------------update:--根据配置信息 对单元格进行自定义设定颜色----------------end-----------------

            //mergeMainCol 合并单元格的条件,通过此列名,进行逐行同内容检测
            // 当检测到改列存在多行内容一致时,将List<String> mergeCols 此处的各列进行单元格合并

            //--------------update:--通过读取配置进行单元格合并----------------start-----------------
            String mergeMainCol = (String)StringUtil.ifNullToBlank((String) json.get("mergeMainCol"));
            @SuppressWarnings("unchecked")
            List<String> mergeCols = (List<String>)json.get("mergeCols");
            List<Integer> mergecellnums = new ArrayList<>();
            if(null != mergeCols && mergeCols.isEmpty()){
                for(String item:mergeCols){
                    mergecellnums.add(getMyindexFromAttrNames(item));
                }
            }
            if(com.github.pagehelper.StringUtil.isNotEmpty(mergeMainCol)){
                int cellnum = getMyindexFromAttrNames(mergeMainCol);
                for(int startRowIndex = 3;startRowIndex <(dataList.size()+2);startRowIndex++){
                    HSSFCell currentCell = sheet.getRow(startRowIndex).getCell(cellnum);
                    String cur = CommonUtil.getStringFromCell(currentCell);
                    Integer endRowIndex = findlastIndexFromCell(sheet,currentCell,(dataList.size()+3),cur,cellnum);
                    for(Integer itemcellnum:mergecellnums){
                        CellRangeAddress address = new CellRangeAddress(startRowIndex,endRowIndex,itemcellnum,itemcellnum);
                        sheet.addMergedRegion(address);
                        RegionUtil.setBorderBottom(1, address, sheet, workbook);
                        RegionUtil.setBorderLeft(1,address,sheet,workbook);
                        RegionUtil.setBorderRight(1,address,sheet,workbook);
                        RegionUtil.setBorderTop(1,address,sheet,workbook);
                    }
                    startRowIndex = endRowIndex;
                }
            }
            //-------------update:--通过读取配置进行单元格合并----------------end-----------------

            //-----------update:------------添加解释说明------------------start-----------
            List<String> remarkList = (List<String>)json.get("remarkList");
            if(null != remarkList){
                for(int i=0;i<remarkList.size();i++){
                    int rownum = dataList.size()+(3+i);
                    HSSFRow rowCell = sheet.createRow(rownum);
                    HSSFCell cell = rowCell.createCell(0,HSSFCell.CELL_TYPE_STRING);
                    cell.setCellStyle(bluerowCellStyle);
                    cell.setCellValue(remarkList.get(i));
                    CellRangeAddress address = new CellRangeAddress(rownum,rownum,0,attrNames.length-1);
                    sheet.addMergedRegion(address);
                    RegionUtil.setBorderBottom(1, address, sheet, workbook);
                    RegionUtil.setBorderLeft(1,address,sheet,workbook);
                    RegionUtil.setBorderRight(1,address,sheet,workbook);
                    RegionUtil.setBorderTop(1,address,sheet,workbook);
                }
            }
            //-----------update:------------添加解释说明------------------end-----------
            //文件写入输出流
            if(workbook != null){
                OutputStream out = response.getOutputStream();
                String excelName = URLEncoder.encode(fileName+".xls","utf-8");
                String headStr = "attachment;filename=\""+excelName+"\"";
                response.setContentType("application/vnd.ms-excel");
                response.setHeader("Content-Disposition", headStr);
                workbook.write(out);
                if(out != null){
                    out.close();
                    response.flushBuffer();
                }
            }

        }catch (Exception e){
            e.printStackTrace();
        }


    }
public HSSFCellStyle getBlueStyle(HSSFWorkbook workbook){
    //  设置字体
    HSSFFont font = workbook.createFont();
    // 设置字体大小
    font.setFontHeightInPoints((short)10);
    font.setFontName("黑体");
    font.setBoldweight(HSSFColor.BLACK.index);
    font.setColor(HSSFColor.BLUE.index);
    // 设置样式；
    HSSFCellStyle style = workbook.createCellStyle();
    // 设置底边框
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    // 设置底边框颜色
    style.setBottomBorderColor(HSSFColor.BLACK.index);
    // 设置左边框
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    // 设置左边框颜色
    style.setLeftBorderColor(HSSFColor.BLACK.index);
    // 设置右边框
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    // 设置右边框颜色
    style.setRightBorderColor(HSSFColor.BLACK.index);
    // 设置顶边框
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    // 设置顶边框颜色
    style.setTopBorderColor(HSSFColor.BLACK.index);
    // 在样式用应用设置的字体
    style.setFont(font);
    // 设置自动换行
    style.setWrapText(true);
    // 设置水平对齐的样式为居中对齐
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    // 设置垂直对齐的样式为居中对齐
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    return style;


}
    public HSSFCellStyle getRedStyle(HSSFWorkbook workbook){
        //  设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short)10);
        font.setFontName("黑体");
        font.setBoldweight(HSSFColor.RED.index);

        // 设置样式；
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体
        style.setFont(font);
        // 设置自动换行
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;


    }
    public int getMyindexFromAttrNames(String mergeMainCol){
        int result = 0;
        for (int i = 0; i < attrNames.length; i++) {
            if (StringUtil.equal((String) StringUtil.ifNullToBlank((String) attrNames[i]), mergeMainCol)) {
                result = i;
                break;
            }
        }
        return result;
    }
    public HSSFCellStyle getNormalBlueStyle(HSSFWorkbook workbook){
        //  设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short)10);
        font.setFontName("黑体");
        font.setColor(HSSFColor.BLUE.index);
        // 设置样式；
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体
        style.setFont(font);
        // 设置自动换行
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;


    }
    public HSSFCellStyle getPurpleTitleRowStyle(HSSFWorkbook workbook){
        //  设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short)11);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("黑体");
        // 设置字体为白色
        font.setColor(HSSFColor.WHITE.index);
        // 设置样式；
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体
        style.setFont(font);
        // 设置自动换行
        style.setWrapText(true);
        // 底色设置为紫色
        HSSFPalette palette = workbook.getCustomPalette();
        palette.setColorAtIndex(HSSFColor.PINK.index, (byte)112, (byte)48, (byte)160);
        style.setFillBackgroundColor(HSSFColor.PINK.index);
        style.setFillForegroundColor(HSSFColor.PINK.index);
        style.setFillPattern(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;


    }

    public HSSFCellStyle getStyle(HSSFWorkbook workbook){
        //  设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short)10);
        font.setFontName("黑体");
        // 设置样式；
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体
        style.setFont(font);
        // 设置自动换行
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }
    public HSSFCellStyle getTitleRowStyle(HSSFWorkbook workbook){
        //  设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("黑体");
        // 设置样式；
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体
        style.setFont(font);
        // 设置自动换行
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }
    public boolean isNumeric(String value){
        if (value.indexOf("-") > 0) {
            return false;
        }
        String reg = "-?[0-9]+.*[0-9]*";
        Pattern pattern = Pattern.compile(reg);
        Matcher isNum = pattern.matcher(value);
        return isNum.matches();
    }
   public Integer findlastIndexFromCell(HSSFSheet sheet ,HSSFCell currentCell,Integer maxRowNum,String curvalue,int cellnum){

       Integer result = currentCell.getRowIndex();
       for(int i = (currentCell.getRowIndex()+1);i<maxRowNum;i++){
           HSSFCell newxCell = sheet.getRow(i).getCell(cellnum);
           String nextCellvalue = CommonUtil.getStringFromCell(newxCell);
           if(nextCellvalue.equals(curvalue)){
               result++;
           }else {
               break;
           }
       }
        return result;
   }
    public Boolean comPareValueWithlistByData(int startRowIndex, String key, String value, String keyWord){
        Boolean result = false;
        int index = startRowIndex - 3;
        if (dataList.size() > index) {
            Object item = dataList.get(index);
            JSONObject jsonObject = (JSONObject) JSONObject.toJSON(item);
            if (jsonObject.containsKey(key)) {
                String jsonValue = (String) StringUtil.ifNullToBlank((String) jsonObject.get(key));
                if (com.github.pagehelper.StringUtil.isNotEmpty(keyWord)) {
                    result = StringUtil.equal(jsonValue, keyWord);
                }
                if (com.github.pagehelper.StringUtil.isNotEmpty(value)) {
                    result = StringUtil.equal(jsonValue, value);
                }
            }
        }
        return result;
    }
}
class Num{
    public int num;
    public int n;
}